package DBHandle;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;

import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;

public class EditVM
{
	private InputStream inputStream;
	DBConn.DBConnManager db=new DBConn.DBConnManager();
	DBHandle.General dbGeneral=new General();
	ResultSet rs=null;
	Statement stmt=null;
	String sql="";	
	Connection conn=db.getConnection("Mysql");
	
	public InputStream getInputStream() {
		return this.inputStream;
	}
	
	//检查VM是否已存在 
	public String CheckVM() throws Exception
	{
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");
		String[] value=(String[])ActionContext.getContext().getParameters().get("value");
		switch(Integer.parseInt(id[0]))
		{
			case 0:
				sql="select id from qbx_vm where qbx_number = '"+value[0]+"'";
				break;
			case 1:
				sql="select id from qbx_vm where alona_number = '"+value[0]+"'";
				break;
			case 2:
				sql="select id from qbx_vm where venue_code = '"+value[0]+"'";
				break;
		}
		if(conn!=null)
		{
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			rs.last();
			if(rs.getRow()==1)
				inputStream=new StringBufferInputStream(String.valueOf(rs.getInt("id")));
			else 
				inputStream=new StringBufferInputStream("false");
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return "success";
	}
	
	//新建VM
	public String CreateVM() throws Exception
	{
		//获取基本属性
		String model=(String)ActionContext.getContext().getSession().get("model");
		String qbx_number=(String)ActionContext.getContext().getSession().get("qbx_number");
		String alona_number=(String)ActionContext.getContext().getSession().get("alona_number");
		int aisles_number=Integer.parseInt((String)ActionContext.getContext().getSession().get("aisles_number"));
		String venueType=(String)ActionContext.getContext().getSession().get("venue_type");
		String venueCode=(String)ActionContext.getContext().getSession().get("venue_code");
		int unit=Integer.parseInt((String)ActionContext.getContext().getSession().get("unit"));
		String city=(String)ActionContext.getContext().getSession().get("city");
		String region=(String)ActionContext.getContext().getSession().get("region");
		
		int regionID=SelectRegion(region,city);
		int venueID=SelectVenue(venueType);
		int modelID=SelectModel(model);
		
		//添加新VM基本属性
		if(CreateNewVM(qbx_number,alona_number,aisles_number,unit,modelID,venueCode,venueID,regionID))
		{
			//获取VM编号SelectID
			int vmID=dbGeneral.SelectID("qbx_vm", "qbx_number", qbx_number);
			ArrayList<String[]> vm=new ArrayList<String[]>();
			for(int i=1;i<57;i++)
			{
				String[] item=(String[])ActionContext.getContext().getParameters().get("I"+String.valueOf(i));
				String[] price=(String[])ActionContext.getContext().getParameters().get("P"+String.valueOf(i));
				if(item!=null&&(String)item[0]!="")
				{
					String[] temp=new String[3];
					temp[0]=String.valueOf(i);
					temp[1]=item[0];
					temp[2]=price[0];
					vm.add(temp);
				}
			}
			for(String[] temp:vm)
			{
				if(temp!=null)
				{
					int productID=dbGeneral.SelectID("qbx_product","item",temp[1]);
					CreateNewAisles(vmID,Integer.parseInt(temp[0]),productID,Integer.parseInt(temp[2]));
				}
			}
			inputStream=new StringBufferInputStream("success");
		}
		else {
			inputStream=new StringBufferInputStream("failure");
		}
		return Action.SUCCESS;
	}	
	
	//查询VM信息
	public String SearchVM() throws Exception
	{
		String[] type=(String[])ActionContext.getContext().getParameters().get("type");
    	String[] model=(String[])ActionContext.getContext().getParameters().get("model");
    	String[] venueType=(String[])ActionContext.getContext().getParameters().get("venue_type");
    	String[] city=(String[])ActionContext.getContext().getParameters().get("city");
    	String[] region=(String[])ActionContext.getContext().getParameters().get("region");
    	String[] qbxNumber=(String[])ActionContext.getContext().getParameters().get("qbx_number");
    	String[] alonaNumber=(String[])ActionContext.getContext().getParameters().get("alona_number");
    	String[] venueCode=(String[])ActionContext.getContext().getParameters().get("venue_code");
    	sql="select id,qbx_number,alona_number,model_id,aisles_number,venue_code,venue_type,unit_number,region_id from qbx_vm where ";
    	if(type[0].equals("above"))
    	{
    		if(!model[0].equals("All"))
    		{
    			int modelID=dbGeneral.SelectID("qbx_model", "id", model[0]);
    			sql+="model_id = "+modelID+" and ";
    		}
    		if(!venueType[0].equals("All"))
    		{
    			int venueTypeID=dbGeneral.SelectID("qbx_venue_type", "id", venueType[0]);
    			sql+="venue_type = "+venueTypeID+" and ";
    		}
    		if(!city[0].equals("All")&&!region[0].equals("All"))
    		{
    			int regionID=SearchRegion(region[0],city[0]);
    			sql+="region_id = "+regionID+" and ";
    		}
    		else if(!city[0].equals("All"))
    		{
    			int regionID=dbGeneral.SelectID("qbx_region", "city", city[0]);
    			sql+="region_id = "+regionID+" and ";
    		}
    		else if(!region[0].equals("All"))
    		{
    			int regionID=dbGeneral.SelectID("qbx_region", "region", region[0]);
    			sql+="region_id = "+regionID+" and ";
    		}
    	}
    	else if(type[0].equals("bellow"))
    	{
    		if(!qbxNumber[0].equals(""))
    			sql+="qbx_number = '"+qbxNumber[0]+"' and ";
    		else if(!alonaNumber[0].equals(""))
    			sql+="alona_number = '"+alonaNumber[0]+"' and ";
    		else if(!venueCode[0].equals(""))
    			sql+="venue_code = '"+venueCode[0]+"' and ";
    	}
    	sql+="1=1";
    	if(conn!=null)
		{
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
            String aaDataString="{\"aaData\":[";
			while(rs.next())
			{
				String modelItem=dbGeneral.SelectInfoString("name", "qbx_model", "id",String.valueOf(rs.getInt("model_id")));
				String venue_type=dbGeneral.SelectInfoString("name", "qbx_venue_type", "id",String.valueOf(rs.getInt("venue_type")));
				String cityName=dbGeneral.SelectInfoString("city", "qbx_region", "id",String.valueOf(rs.getInt("region_id")));
				String regionName=dbGeneral.SelectInfoString("region", "qbx_region", "id",String.valueOf(rs.getInt("region_id")));
				aaDataString+="[\"<input type='checkbox' class='checkOne' value='"+rs.getInt("id")+"'></input>\",\""
						+rs.getString("qbx_number")+"\",\""+rs.getString("alona_number")+"\",\""+String.valueOf(rs.getInt("aisles_number"))
						+"\",\""+modelItem+"\",\""+rs.getString("venue_code")+"\",\""+venue_type+"\",\""
						+String.valueOf(rs.getInt("unit_number"))+"\",\""+cityName+"\",\""+regionName+"\",\""
						+"<a href='#' id='editOne' value='"+rs.getInt("id")+"'>Edit</a>&nbsp&nbsp"
						+"<a href='#' id='deleteOne' value='"+rs.getInt("id")+"'>Delete</a>"
						+"\"],";
				aaDataString+="";
			}
			if(aaDataString.substring(aaDataString.length()-1).equals(","))
				aaDataString=aaDataString.substring(0,aaDataString.length()-1);
			aaDataString+="]}";
			inputStream=new StringBufferInputStream(aaDataString);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn); 
		}          
    	
		return Action.SUCCESS;
	}
	
	//更新VM信息
	public String UpdateVM() throws Exception
	{
		//获取基本属性
		String[] id=(String[])ActionContext.getContext().getParameters().get("s_id");
    	String[] model=(String[])ActionContext.getContext().getParameters().get("s_model");
    	String[] venueType=(String[])ActionContext.getContext().getParameters().get("s_type");
    	String[] city=(String[])ActionContext.getContext().getParameters().get("s_city");
    	String[] region=(String[])ActionContext.getContext().getParameters().get("s_region");
    	String[] qbxNumber=(String[])ActionContext.getContext().getParameters().get("s_qbxNumber");
    	String[] alonaNumber=(String[])ActionContext.getContext().getParameters().get("s_alonaNumber");
    	String[] venueCode=(String[])ActionContext.getContext().getParameters().get("s_code");
    	String[] unit=(String[])ActionContext.getContext().getParameters().get("s_unit");
    	String[] aislesNumber=(String[])ActionContext.getContext().getParameters().get("s_aisles");
		
		int regionID=SelectRegion(region[0],city[0]);
		int venueID=SelectVenue(venueType[0]);
		int modelID=SelectModel(model[0]);
		
		//更新VM信息
		UpdateNewVM(Integer.parseInt(id[0]),qbxNumber[0],alonaNumber[0],aislesNumber[0],unit[0],modelID,venueCode[0],venueID,regionID);
		DelAisles(Integer.parseInt(id[0]));
		//更新Aisles信息
		ArrayList<String[]> vm=new ArrayList<String[]>();
		for(int i=1;i<57;i++)
		{
			String[] item=(String[])ActionContext.getContext().getParameters().get("I"+String.valueOf(i));
			String[] price=(String[])ActionContext.getContext().getParameters().get("P"+String.valueOf(i));
			if(item!=null&&(String)item[0]!="")
			{
				String[] temp=new String[3];
				temp[0]=String.valueOf(i);
				temp[1]=item[0];
				temp[2]=price[0];
				vm.add(temp);
			}
		}
		for(String[] temp:vm)
		{
			if(temp!=null)
			{
				int productID=dbGeneral.SelectID("qbx_product","item",temp[1]);
				CreateNewAisles(Integer.parseInt(id[0]),Integer.parseInt(temp[0]),productID,Integer.parseInt(temp[2]));
			}
		}
		inputStream=new StringBufferInputStream("success");
		
		return Action.SUCCESS;
	}
	
	//删除VM信息 
	public String DeleteOneVM() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();			
			sql="delete from qbx_aisles where vm_id = '"+id[0]+"'";
			int j=stmt.executeUpdate(sql);
			sql="delete from qbx_vm where id = '"+id[0]+"'";
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//删除多项VM信息
	public String DeleteMoreVM() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			String[] ID=id[0].split("#");
			for(int m=0;m<ID.length;m++)
			{
				stmt=conn.createStatement();			
				sql="delete from qbx_aisles where vm_id = '"+ID[m]+"'";
				int j=stmt.executeUpdate(sql);
				sql="delete from qbx_vm where id = '"+ID[m]+"'";
				int i=stmt.executeUpdate(sql);
				if(i==1)
					inputStream=new StringBufferInputStream("true");
				else 
				{
					inputStream=new StringBufferInputStream("false");
					stmt.close();
					break;
				}
				stmt.close();
			}
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//获取VM对应Aisles信息
	public String SearchAisles() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();
			sql="select A.aisles,A.price,B.item from qbx_aisles A,qbx_product B where A.product_id = B.id and A.vm_id = '"+id[0]+"'";
			rs=stmt.executeQuery(sql);
			String data="[";
			while(rs.next())
			{
				data+="{'aisles':'"+String.valueOf(rs.getInt("A.aisles"))+"','product':'"+rs.getString("B.item")+"','price':'"
					+String.valueOf(rs.getInt("A.price"))+"'},";
			}
			if(data.substring(data.length()-1).equals(","))
				data=data.substring(0,data.length()-1);
			data+="]";
			inputStream=new StringBufferInputStream(data);
			stmt.close();
			rs.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	/*-----私有函数-----*/
	//查询Region ID ，不存在此记录则先添加记录
	private Integer SelectModel(String name) throws Exception
	{
		int modelID=0;
		if(conn!=null)
		{
			sql="select id from qbx_model where name = '"+name+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			rs.last();
			if(rs.getRow()==0)
			{
				sql="insert into qbx_model(name) values('"+name+"')";
				int i=stmt.executeUpdate(sql);
				if(i==1)
				{
					sql="select id from qbx_model where name = '"+name+"'";
					rs=stmt.executeQuery(sql);
					while(rs.next())
					modelID=rs.getInt(1);
				}
			}
			else
			{
				modelID = rs.getInt(1);
			}
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		return modelID;
	}

	//查询Region ID 
	private Integer SearchRegion(String region,String city) throws Exception
	{
		int regionID=0;
		if(conn!=null)
		{
			sql="select id from qbx_region where region = '"+region+"' and city = '"+city+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next())
				regionID = rs.getInt(1);
			rs.close();
			stmt.close();
		}
		return regionID;
	}
	
	//查询Region ID ，不存在此记录则先添加记录
	private Integer SelectRegion(String region,String city) throws Exception
	{
		int regionID=0;
		if(conn!=null)
		{
			sql="select id from qbx_region where region = '"+region+"' and city = '"+city+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			rs.last();
			if(rs.getRow()==0)
			{
				sql="insert into qbx_region(region,city) values('"+region+"' ,'"+city+"')";
				int i=stmt.executeUpdate(sql);
				if(i==1)
				{
					sql="select id from qbx_region where region = '"+region+"' and city = '"+city+"'";
					rs=stmt.executeQuery(sql);
					while(rs.next())
					regionID=rs.getInt(1);
				}
			}
			else
			{
				regionID = rs.getInt(1);
			}
			rs.close();
			stmt.close();
		}
		return regionID;
	}
	
	//查询venue ID，如果不存在此记录则先添加记录
	private Integer SelectVenue(String type) throws Exception
	{
		int venueID=0;
		if(conn!=null)
		{
			sql="select id from qbx_venue_type where name = '"+type+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			rs.last();
			if(rs.getRow()==0)
			{
				sql="insert into qbx_venue_type(name) values ('"+type+"')";
				int i=stmt.executeUpdate(sql);
				if(i==1)
				{
					sql="select id from qbx_venue_type where name = '"+type+"'";
					rs=stmt.executeQuery(sql);
					while(rs.next())
					venueID=rs.getInt(1);
				}
			}
			else
			{
				venueID = rs.getInt(1);
			}
			rs.close();
			stmt.close();
		}
		return venueID;
	}
	
	//添加新VM基本属性
	private boolean CreateNewVM(String qbx_number,String alona_number,int aisles_number,int unit_number,int model_id,String venue_code,int venue_id,int region_id)
					throws Exception
	{
		if(conn!=null)
		{
			sql="insert into qbx_vm(qbx_number,alona_number,aisles_number,unit_number,model_id,venue_code,venue_type,region_id)" +
				" values('"+qbx_number+"','"+alona_number+"',"+aisles_number+","+unit_number+","+model_id+",'"+venue_code+"',"+venue_id+","+region_id+")";
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
			if(i==1)
				return true;
		}
		return false;		
	}
	
	//删除VM对应的Aisles信息
	private void DelAisles(int vmID) throws Exception
	{
		if(conn!=null)
		{
			sql="delete from qbx_aisles where vm_id = "+vmID;
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
	}
	
	//添加新VM对应的Aisles信息
	private boolean CreateNewAisles(int vmID,int aisles,int productID,int price) throws Exception
	{
		if(conn!=null)
		{
			sql="insert into qbx_aisles(vm_id,aisles,product_id,price)" +
				" values("+vmID+",'"+aisles+"',"+productID+",'"+price+"')";
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
			if(i==1)
				return true;
		}
		
		return false;	
	}
	
	//更新VM基本属性
	private boolean UpdateNewVM(int id,String qbx_number,String alona_number,String aisles_number,String unit_number,int model_id,String venue_code,int venue_id,int region_id)
					throws Exception
	{
		if(conn!=null)
		{
			sql="update qbx_vm set qbx_number = '"+qbx_number+"',alona_number = '"+alona_number+"',aisles_number= '"+aisles_number
				+"',unit_number = '"+unit_number+"',model_id = '"+model_id+"',venue_code = '"+venue_code+"',venue_type = '"+venue_id
				+"',region_id = '"+region_id+"' where id = '"+id+"'";
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
			if(i==1)
				return true;
		}
		return false;		
	}
	
}